<?php declare(strict_types=1);

namespace App\Services\Order;

use App\Extensions\oss\Oss;
use App\Jobs\OrderQueryExportJob;
use App\Models\Bi\BiOrder;
use App\Models\Bi\BiRefundOrder;
use App\Models\MerchantStore;
use App\Models\Order;
use App\Models\OrderGoodsDetail;
use App\Models\StoreAllPayWayList;
use App\Models\StoreGoods;
use App\Param\User\UserParams;
use App\Repository\OrderExportRepo;
use App\Services\PrintSqlService;
use App\Util\ExeclUtil;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;
use Illuminate\Support\Facades\Schema;
use ZipArchive;
class UserService
{


    const TmpTableName = "tmp_orders";

    public function setWhere($query, array $where, string $table = ""): void
    {
        if (!empty($table)) {
            $table .= ".";
        }
        if (isset($where["user_id"]) && $where["user_id"] != "") {
            if (is_array($where["user_id"])) {
                $query->whereIn($table . "user_id", $where["user_id"]);
            } else {
                $query->where($table . "user_id", $where["user_id"]);
            }
        }

        if (!empty($where["store_ids"])) {
            $query->whereIn($table . "store_id", $where["store_ids"]);
        }

        if (!empty($where["store_id"])) {
            if (is_array($where["store_id"])) {
                $query->whereIn($table . "store_id", $where["store_id"]);
            } else {
                $query->where($table . "store_id", $where["store_id"]);
            }
        }

        if (!empty($where["pay_status"])) {
            $query->where($table . "pay_status", $where["pay_status"]);
        }

        if (!empty($where["company"])) {
            $query->where($table . "company", $where["company"]);
        }

        if (!empty($where["start_date"])) {
            $query->where($table . "created_at", ">=", $where["start_date"]);
        }

        if (!empty($where["end_date"])) {
            $query->where($table . "created_at", "<=", $where["end_date"]);
        }

        if (!empty($where["total_amount"])) {
            $query->where($table . "total_amount", $where["total_amount"]);
        }
        if (!empty($where["device_id"])) {
            $query->where($table . "device_id", $where["device_id"]);
        }
        if (!empty($where["out_trade_no"])) {
            $query->where($table . "out_trade_no", $where["out_trade_no"]);
        }

        if (!empty($where["trade_no"])) {
            $query->where($table . "trade_no", $where["trade_no"]);
        }

        if (!empty($where["ways_source"])) {
            $query->where($table . "ways_source", $where["ways_source"]);
        }

        if (!empty($where["qwx_no"])) {
            $query->where($table . "qwx_no", $where["qwx_no"]);
        }

        if (!empty($where["auth_code"])) {
            $query->where($table . "auth_code", $where["auth_code"]);
        }
    }


    public function getTableName(string $time, bool $as = true, string $prefix = ""): string
    {
        $tableName = $prefix . "orders_" . date("Ym", strtotime($time));
        if (!$as) {
            return $tableName;
        }
        return $tableName . " as " . $prefix . self::TmpTableName;
    }

    public function orderQuery(array $where, bool $page = true): array
    {
        if (empty($where["start_date"]) || $where["start_date"] >= date("Y-m-d", time()) . " 00:00:00") {
            $prefix = "";
            $query = Order::query()->from("orders as " . self::TmpTableName);
        } else {
            $prefix="bi_";
            if (!Schema::connection("bi")->hasTable($this->getTableName($where["start_date"], false))) {
                throw new \Exception("暂无数据");
            }
            $query = BiOrder::query()->from(DB::raw($this->getTableName($where["start_date"], true, $prefix) . " FORCE INDEX(created_at)"));
        }
        $this->setWhere($query, $where, self::TmpTableName);
        if ($page) {
            $total = $query->count();
            $query->select([self::TmpTableName . ".*", "refund_orders.created_at as refund_time"]);
            $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");
            $list = $query->offset(($where["page"] - 1) * $where["pagesize"])
                ->limit($where["pagesize"])
                ->orderBy(self::TmpTableName . ".created_at", "desc")
                ->get()->toArray();
//            $query = str_replace(array('%', '?'), array('%%', '%s'), $list->toSql());
//            $query = vsprintf($query, $list->getBindings());
//            dd($query);
        } else {
            $total = $query->count();
            if ($total > 1000000) {
                throw new \Exception("导出的数据条数大于100W,请设置详细的条件");
            }
            $query->select([self::TmpTableName . ".*", "refund_orders.created_at as refund_time"]);
            $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");
            $list = $query->orderBy(self::TmpTableName . ".created_at", "desc")->cursor();
        }
        if (!$list instanceof \Generator) {
            $company = StoreAllPayWayList::query()->get()->toArray();
            $company = array_combine(array_column($company, "company"), $company);
            $outTradeNoArray = array_unique(array_column($list, "out_trade_no"));
            $goodsCoupons = [];
            if (!empty($outTradeNoArray)) {
//                order_goods_details
                $goodsCoupons = OrderGoodsDetail::query()
                    ->select([
                        "out_trade_no", "coupon_type",
                        DB::raw("sum(coupon_amount) as coupon_amount"),
                        DB::raw("count(1) as c")
                    ])
                    ->whereIn("out_trade_no", $outTradeNoArray)
                    ->where("status", 1)
                    ->groupBy(["out_trade_no", "coupon_type"])
                    ->get()->toArray();
                $goodsCoupons = array_combine(array_column($goodsCoupons, "out_trade_no"), $goodsCoupons);
            }
            foreach ($list as $k => $v) {
                $v["company_name"] = "";
                if (isset($company[$v["company"]])) {
                    $v["company_name"] = $company[$v["company"]]["company_desc"];
                }
                $v["actual_amount"] = bcsub((string)$v["total_amount"], (string)$v["refund_amount"], 2);

                $v["refund_type"] = 0;
                $v["refund_type_name"] = "";
                if ($v["pay_status"] == 6) {
                    $v["refund_type"] = 1;
                    $v["refund_type_name"] = "当日退";
                    if (isset($v["created_at"]) && isset($v["refund_time"])) {
                        if (date("Ymd", strtotime((string)$v["created_at"])) != date("Ymd", strtotime((string)$v["refund_time"]))) {
                            $v["refund_type"] = 2;
                            $v["refund_type_name"] = "隔日退";
                        }
                        if (date("Ym", strtotime((string)$v["created_at"])) != date("Ym", strtotime((string)$v["refund_time"]))) {
                            $v["refund_type"] = 3;
                            $v["refund_type_name"] = "隔月退";
                        }
                    }
                }
                $v["coupon_type"] = 0;
                $v["coupon_amount"] = 0;
                if (!empty($goodsCoupons) && isset($goodsCoupons[$v["out_trade_no"]]) && $v["pay_status"] == 1) {
                    $goodsCoupon = $goodsCoupons[$v["out_trade_no"]] ?? [];
                    $v["coupon_type"] = $goodsCoupon["coupon_type"] == "COUPON" ? 1 : 2;
                    $v["coupon_amount"] = $goodsCoupon["coupon_amount"] ?? 0;
                }
                if ($v["coupon_type"] == 0 && $v["discount_amount"] > 0 && $v["pay_status"] == 1) {
                    $v["coupon_type"] = 1;
                    $v["coupon_amount"] = $v["discount_amount"];
                }
                $list[$k] = $v;
            }
        }
        return [
            "total" => $total,
            "list" => $list
        ];
    }

    public function orderQueryExport(array $where): array
    {
        ini_set('memory_limit', '512M');
        set_time_limit(0);
        if (empty($where["start_date"]) || $where["start_date"] >= date("Y-m-d", time()) . " 00:00:00") {
            $prefix = "";
            $key = "";
            $query = DB::connection();
            $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
            $query = $query->table("orders as " . self::TmpTableName);
        } else {
            $prefix = "bi_";
            if (!Schema::connection("bi")->hasTable($this->getTableName($where["start_date"], false))) {
                throw new \Exception("暂无数据");
            }
            //历史导出记录记录下来
//            $key = md5(json_encode($where));
//            $redis = Redis::connection('default');
//            $url = $redis->get("userOrderQueryExportV3:" . $key);
//            if (!empty($url)) {
//                return [
//                    "url" => $url
//                ];
//            }
            $query = DB::connection("bi");
            $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
            $query = $query->table(DB::raw($this->getTableName($where["start_date"], true, $prefix) . " FORCE INDEX(created_at)"));
        }
        $this->setWhere($query, $where, self::TmpTableName);
        $total = $query->count();
        if ($total >= 10000000) {
            throw new \Exception("导出的数据条数大于100W,请设置详细的条件");
        }
        $query->select([self::TmpTableName . ".*", "refund_orders.created_at as refund_time",
            "order_goods_details.coupon_amount as goods_coupon_amount", "order_goods_details.coupon_type as goods_coupon_type"]);
        $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");

        $goodsQuery = DB::table($prefix . "order_goods_details")->select([
            "out_trade_no", "coupon_type",
            DB::raw("sum(coupon_amount) as coupon_amount"),
        ])
            ->where("status", 1)
            ->groupBy(["out_trade_no", "coupon_type"]);
        $this->setWhere($goodsQuery, [
            "user_id" => $where["user_id"],
            "store_id" => $where["store_ids"],
            "start_date" => $where["start_date"],
            "end_date" => $where["end_date"]
        ]);
        $query->leftJoinSub($goodsQuery, $prefix . "order_goods_details", self::TmpTableName . ".out_trade_no", "=", "order_goods_details.out_trade_no");
//        $query->leftJoin("order_goods_details", self::TmpTableName . ".out_trade_no", "=", "order_goods_details.out_trade_no");
        // Insert bindings into query
        $orders = $query->orderBy(self::TmpTableName . ".created_at", "desc")->cursor();
        $company = StoreAllPayWayList::query()->get()->toArray();
        $company = array_combine(array_column($company, "company"), $company);
        $util = new ExeclUtil();
//        $orders = $util->loadGoods($orders);
        //数据导出
        $title = [
            '门店ID', '门店名称', "设备ID", '收银员', '订单号', "原订单号", '订单金额', '退款金额', "实收金额", '费率', '手续费', '支付方式', '支付状态', '付款时间', '通道', '退款时间', "退款类型", '备注', "买家ID", "优惠券类型", "优惠券金额"
        ];
        $fileName = "订单导出" . ($where["month"] ?? date("Y-m-d")) . "_" . time() . ".csv";
        $filePath = public_path("/uploads/csv/" . date("Y/md", time()) . "/");
        //写入excel
        $util->generatorExport($filePath, $fileName, $title, $orders, function ($order) use ($company) {
            $refund_type_name = "";
            if ($order->pay_status == 6) {
                $refund_type_name = "当日退";
                if (date("Ymd", strtotime((string)$order->created_at)) != date("Ymd", strtotime((string)$order->refund_time))) {
                    $refund_type_name = "隔日退";
                }
                if (date("Ym", strtotime((string)$order->created_at)) != date("Ym", strtotime((string)$order->refund_time))) {
                    $refund_type_name = "隔月退";
                }
            }
            $coupon_type = 0;
            $coupon_type_text = "无";
            $coupon_amount = 0;

            if (!empty($order->goods_coupon_type) && $order->pay_status == 1) {
                $coupon_type = $order->goods_coupon_type == "COUPON" ? 1 : 2;
                $coupon_type_text = $order->goods_coupon_type == "COUPON" ? "预充" : "免充";
                $coupon_amount = $order->goods_coupon_amount ?? 0;
            }
            if ($coupon_type == 0 && $order->discount_amount > 0 && $order->pay_status == 1) {
                $coupon_type = 1;
                $coupon_type_text = "预充";
                $coupon_amount = $order->discount_amount;
            }
            return [
                $order->store_id,
                $order->store_name,
                $order->device_id,
                $order->merchant_name,
                "'" . $order->out_trade_no,
                $order->trade_no,
                $order->total_amount,
                $order->refund_amount,
                bcsub((string)$order->total_amount, (string)$order->refund_amount, 2),
                $order->rate . "%",
                $order->fee_amount,
                $order->ways_type_desc,
                $order->pay_status_desc,
                $order->pay_time,
                isset($company[$order->company]) ? $company[$order->company]["company_desc"] : "",
                $order->refund_time,
                $refund_type_name,
                $order->remark,
                $order->buyer_id,
                $coupon_type_text,
                $coupon_amount
            ];
        });
        $url = $util->uploadOss($filePath . $fileName, $fileName);
        //删除文件
        if (file_exists($filePath . $fileName)) {
//            unlink($filePath . $fileName);
        }
        if (!empty($key)) {
//            $redis->set("userOrderQueryExportV3:" . $key, $url, "", 3600 * 24 * 20);
        }
        //删除fileName
        return [
            "url" => $url
        ];
    }
    /**
     * 新版导出
     * @param array $where
     * @param int $user_id
     * @param array $queryData
     * @return void
     * @throws \Exception
     */
    public function newOrderQueryExport(array $where,int $user_id)
    {

        /**
         * 生成导出订单号
         */
        $uiqid = date("YmdHi", time()).$user_id;

        /**
         * 防止前端重复提交
         */
        $redis = Redis::connection('default');

        if (empty($redis))
            throw new \Exception("服务异常");

        if ($redis->exists('system_order_query_export:'.$uiqid))
            throw new \Exception("请稍后再试！");

        $redis->setex("system_order_query_export:" . $uiqid, 30, $user_id);
        return $this->excelDownLoad($where);
    }

    /**
     * 获取导出记录列表
     * @param $page
     * @param $pageSize
     * @param $where
     * @return array
     */
    public function getOrderExportListService($page,$pageSize,$where)
    {
        $list = OrderExportRepo::getInstance()->getList($where);
        return $this->toReturnList($list,(int)$page,(int)$pageSize);
    }

    /**
     * 获取分页
     * @param $coll
     * @param int $page
     * @param int $pageSize
     * @return array
     */
    protected function toReturnList($coll, int $page = 0, int $pageSize = 20)
    {
        $newPage = ($page -1) * $pageSize;
        return [
            'total' => $coll->count(),
            'list' => $coll->offset($newPage)->limit($pageSize)->get()
        ];
    }

    /**
     * 修改导出状态
     * @param UserParams $params
     * @return bool|int
     */
    public function OrderExportSaveExportStatusService(UserParams $params)
    {
        $id = $params->getId();
        return OrderExportRepo::getInstance()->saveData(['export_status'=>1],(int)$id);
    }

    /**
     * 生成EXCEL、上传阿里云oss
     * @param array $where
     * @return string|void
     * @throws \Exception
     */
    protected function excelDownLoad(array $where)
    {
        ini_set('memory_limit', '500M');
        set_time_limit(0);

        /**
         * 定义查询table
         */
        if (empty($where["start_date"]) || $where["start_date"] >= date("Y-m-d", time()) . " 00:00:00") {
            $prefix = "";
            $query = DB::connection();
            $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
            $query = $query->table("orders as " . self::TmpTableName);
        } else {
            $prefix = "bi_";
            if (!Schema::connection("bi")->hasTable($this->getTableName($where["start_date"], false))) {
                throw new \Exception("暂无数据");
            }
            $query = DB::connection("bi");
            $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
            $query = $query->table(DB::raw($this->getTableName($where["start_date"], true, $prefix)));
        }

        /**
         * 设置查询条件
         */
        $this->setWhere($query, $where, self::TmpTableName);



        $query->select([
            self::TmpTableName . ".trade_no",
            self::TmpTableName.'.store_id',
            self::TmpTableName.'.out_trade_no',
            self::TmpTableName.'.created_at',
            self::TmpTableName.'.store_name',
            self::TmpTableName.'.device_id',
            self::TmpTableName.'.merchant_name',
            self::TmpTableName.'.total_amount',
            self::TmpTableName.'.refund_amount',
            self::TmpTableName.'.rate',
            self::TmpTableName.'.fee_amount',
            self::TmpTableName.'.ways_type_desc',
            self::TmpTableName.'.pay_status_desc',
            self::TmpTableName.'.pay_time',
            self::TmpTableName.'.company',
            self::TmpTableName.'.remark',
            self::TmpTableName.'.buyer_id',
            self::TmpTableName.'.pay_status',
            self::TmpTableName.'.discount_amount',
            "refund_orders.created_at as refund_time",
            "order_goods_details.coupon_amount as goods_coupon_amount",
            "order_goods_details.coupon_type as goods_coupon_type"
        ]);
        $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");

        $goodsQuery = DB::table($prefix . "order_goods_details")->select([
            "out_trade_no", "coupon_type",
            DB::raw("sum(coupon_amount) as coupon_amount"),
        ])
            ->where("status", 1)
            ->groupBy(["out_trade_no", "coupon_type"]);
        $this->setWhere($goodsQuery, [
            "user_id" => $where["user_id"],
            "store_id" => $where["store_ids"],
            "start_date" => $where["start_date"],
            "end_date" => $where["end_date"]
        ]);
        $query->leftJoinSub($goodsQuery, $prefix . "order_goods_details", self::TmpTableName . ".out_trade_no", "=", "order_goods_details.out_trade_no");
        $orders = $query->orderBy(self::TmpTableName . ".created_at", "desc")->cursor();

        $company = StoreAllPayWayList::query()->get()->toArray();
        $company = array_combine(array_column($company, "company"), $company);

        /**
         * 定义表Tiele
         */
        $title = [
            '门店ID', '门店名称', "设备ID", '收银员', '订单号', "原订单号", '订单金额', '退款金额', "实收金额", '费率', '手续费', '支付方式', '支付状态', '付款时间', '通道', '退款时间', "退款类型", '备注', "买家ID", "优惠券类型", "优惠券金额"
        ];
        $fileName = "订单导出" . ($where["month"] ?? date("Y-m-d")) . "_" . time() . ".csv";
        $filePath = public_path("/uploads/csv/" . date("Y/md", time()) . "/");
        Log::info('商户订单导出文件保存地址'.$filePath.$fileName);

        /**
         * 写入excel
         */
        try {
            Log::info('商户订单导出文件开始');
            $this->flushFile($filePath, $fileName, $title, $orders, function ($order) use ($company) {

                $refund_type_name = "";
                if ($order->pay_status == 6) {
                    $refund_type_name = "当日退";
                    if (empty($order->refund_time))
                    {
                        $refund_type_name = "当日退";
                    }else{
                        if (date("Ymd", strtotime((string)$order->created_at)) != date("Ymd", strtotime((string)$order->refund_time))) {
                            $refund_type_name = "隔日退";
                        }
                        if (date("Ym", strtotime((string)$order->created_at)) != date("Ym", strtotime((string)$order->refund_time))) {
                            $refund_type_name = "隔月退";
                        }
                    }

                }
                $coupon_type = 0;
                $coupon_type_text = "无";
                $coupon_amount = 0;

                if (!empty($order->goods_coupon_type) && $order->pay_status == 1) {
                    $coupon_type = $order->goods_coupon_type == "COUPON" ? 1 : 2;
                    $coupon_type_text = $order->goods_coupon_type == "COUPON" ? "预充" : "免充";
                    $coupon_amount = $order->goods_coupon_amount ?? 0;
                }
                if ($coupon_type == 0 && $order->discount_amount > 0 && $order->pay_status == 1) {
                    $coupon_type = 1;
                    $coupon_type_text = "预充";
                    $coupon_amount = $order->discount_amount;
                }
                return [
                    iconv('utf-8','gbk',$order->store_id."\t"),
                    $order->store_name,
                    iconv('utf-8','gbk',$order->device_id."\t"),
                    $order->merchant_name,
                    "'" . $order->out_trade_no,
                    iconv('utf-8','gbk',$order->trade_no."\t"),
                    $order->total_amount,
                    $order->refund_amount,
                    bcsub((string)$order->total_amount, (string)$order->refund_amount, 2),
                    $order->rate . "%",
                    $order->fee_amount,
                    $order->ways_type_desc,
                    $order->pay_status_desc,
                    $order->pay_time,
                    isset($company[$order->company]) ? $company[$order->company]["company_desc"] : "",
                    $order->refund_time,
                    $refund_type_name,
                    $order->remark,
                    $order->buyer_id,
                    $coupon_type_text,
                    $coupon_amount
                ];
            });

            $fileSize = filesize($filePath.$fileName);
            Log::info('商户订单导出文件大小'.$fileSize);

            /**
             * 对csv进行压缩
             */
            $zipArc = new ZipArchive();
            $zipName = $filePath.explode('.',$fileName)[0].'.zip';
            if ($zipArc->open($zipName, ZipArchive::CREATE) === false) {
                Log::info('商户订单导出文件打开csv失败');
                return '';
            }
            $zipFilaPath = $filePath.$fileName;
            // 加密文件 此处文件名及路径是压缩包内的
            $zipFile = '导出数据/'.$fileName;
            $zipArc->addFile($zipFilaPath,$zipFile);
            $zipArc->close();
            Log::info("[商户订单导出文件压缩关闭]");

            /**
             * 上传至阿里云OSS
             */
//            $result = Oss::getInstance()->uploadDoc(explode('.',$fileName)[0].'.zip',$zipName);
//            $url = '';
//            if (!empty($result)) {
//                $url = $result['oss-request-url']?? "";
//                $url = explode('?',$url)[0];
//                Log::info('商户订单导出oss链接'.$url);
//            }

            $url = secure_url("/uploads/csv/" . date("Y/md", time()) . "/".explode('.',$fileName)[0].'.zip');
        }catch (\Exception $e){
            Log::error('商户订单导出异常'.$e->getMessage());
            $url = '';
        }

        /**
         * 删除excel文件
         */
        if (file_exists($filePath . $fileName)) {
            unlink($filePath . $fileName);
        }
        /**
         * 删除zip文件
         */
//        if (file_exists($filePath .explode('.',$fileName)[0].'.zip')) {
//            unlink($filePath .explode('.',$fileName)[0].'.zip');
//        }
        return ['url'=>$url];
    }

    /**
     * 文件缓冲
     * @param string $filePath
     * @param string $fileName
     * @param $title
     * @param \Generator $orders
     * @param callable $callable
     * @return bool
     */
    protected function flushFile(string $filePath, string $fileName, $title, \Generator $orders, callable $callable)
    {
        try {
            if (!is_dir($filePath)) {
                mkdir($filePath, 0777, true);
            }
            $file = $filePath . $fileName;
            $fileObj = fopen($file, 'w');
            fwrite($fileObj, "\xEF\xBB\xBF"); // UTF-8 BOM
            fputcsv($fileObj, $title);
            $counter = 0;
            foreach ($orders as $order) {
                fputcsv($fileObj, $callable($order));
                // 每5000行刷新一次文件缓冲
                if (++$counter % 5000 === 0) {
                    fflush($fileObj);
                    flush();
                    gc_collect_cycles();
                }
                unset($order);
            }
            fclose($fileObj);

            return true;
        }catch (\Exception $e){
            Log::error('商户订单导出写入文件失败'.$e->getMessage());
            return false;
        }
    }
}